<?php

/**
 * reportsCustomer actions.
 *
 * @package    erpcrm
 * @subpackage reportsCustomer
 * @author     Your name here
 * @version    SVN: $Id: actions.class.php 23810 2009-11-12 11:07:44Z Kris.Wallsmith $
 */
class reportsCustomerActions extends sfActions
{
 /**
  * Executes index action
  *
  * @param sfRequest $request A request object
  */
  public function executeIndex(sfWebRequest $request){}
  
  public function executeSOA(sfWebRequest $request){
    $customer_iden = $request->getParameter('contact_iden');
    $this->contact = ContactPeer::retrieveByPk($customer_iden);
    $this->SOAForm = new mySOAForm();
    $this->SOAForm->setDefault('customer_iden',$customer_iden);
  }
    
  public function executeDoSOA(sfWebRequest $request){
    $parameters = $request->getParameter('rkey_SOA');
    $SOAForm = new mySOAForm();
    $SOAForm->bind($parameters);
    if($SOAForm->isValid()){
      $customer_iden = $SOAForm->getValue('customer_iden');
      $owner_iden = $SOAForm['owner_iden']->getValue();
      $date_from = $SOAForm['date_from']->getValue();
      $this->date_from = $date_from['year'].'-'.$date_from['month'].'-'.$date_from['day'];
      $date_to = $SOAForm['date_to']->getValue();
      $this->date_to = $date_to['year'].'-'.$date_to['month'].'-'.$date_to['day'];
      $accountOwner = AccountOwnerPeer::retrieveByPk($owner_iden);
      $this->owner = $accountOwner->getContact();
      $accountCustomer = AccountCustomerPeer::retrieveByPk($customer_iden);
      $this->contact = $accountCustomer->getContact();
      $con = Propel::getConnection();
    
      $this->receivables = myQuery::getReceivables($con,$customer_iden,$owner_iden,$this->date_from,$this->date_to);

      $this->cash_advances = myQuery::getCashAdvances($con,$customer_iden,$owner_iden);
    
      $this->check_advances = myQuery::getCheckAdvances($con,$customer_iden,$owner_iden);
      
      $this->setLayout('nomenu');
    }else{
      return $this->renderText('Errors encountered.');
    }

  }
    
  public function executeDetailedReceivables(sfWebRequest $request){
    $customer_iden = $request->getParameter('contact_iden');
    $this->contact = ContactPeer::retrieveByPk($customer_iden);
    $this->detailedReceivablesForm = new myDetailedReceivablesForm();
    $this->detailedReceivablesForm->setDefault('customer_iden',$customer_iden);
  }

  public function executeDoDetailedReceivables(sfWebRequest $request){
    $parameters = $request->getParameter('rkey_detailed_receivables');
    $detailedReceivablesForm = new myDetailedReceivablesForm();
    $detailedReceivablesForm->bind($parameters);
    if($detailedReceivablesForm->isValid()){
      $this->asof_date = $detailedReceivablesForm->getValue('asof_date');
      $customer_iden = $detailedReceivablesForm['customer_iden']->getValue();
      $owner_iden = $detailedReceivablesForm['owner_iden']->getValue();
      $accountOwner = AccountOwnerPeer::retrieveByPk($owner_iden);
      $this->owner = $accountOwner->getContact();
      $accountCustomer = AccountCustomerPeer::retrieveByPk($customer_iden);
      $this->contact = $accountCustomer->getContact();
      $connection = Propel::getConnection();
      $sql_asof_date = date('Y-m-d',strtotime($this->asof_date)); 
      $query = 
        'SELECT i.invoice_iden,d.document_date,date_add(d1.document_date,interval i.days_due day) due_date,i.invoice_type,i.invoice_id, '.
	'sum(case when datediff(\''.$sql_asof_date.'\',date_add(d1.document_date,interval i.days_due day))<=0 then lt.debit-lt.credit else 0 end) total_notdue, '.
	'sum(case when datediff(\''.$sql_asof_date.'\',date_add(d1.document_date,interval i.days_due day))>0 and datediff(\''.$sql_asof_date.'\',date_add(d1.document_date,interval i.days_due day))<=30 then lt.debit-lt.credit else 0 end) total_due30, '.
	'sum(case when datediff(\''.$sql_asof_date.'\',date_add(d1.document_date,interval i.days_due day))>30 and datediff(\''.$sql_asof_date.'\',date_add(d1.document_date,interval i.days_due day))<=60 then lt.debit-lt.credit else 0 end) total_due60, '.
	'sum(case when datediff(\''.$sql_asof_date.'\',date_add(d1.document_date,interval i.days_due day))>60 and datediff(\''.$sql_asof_date.'\',date_add(d1.document_date,interval i.days_due day))<=90 then lt.debit-lt.credit else 0 end) total_due90, '.
	'sum(case when datediff(\''.$sql_asof_date.'\',date_add(d1.document_date,interval i.days_due day))>90 then lt.debit-lt.credit else 0 end) total_due120, '.
        'sum(lt.debit-lt.credit) AS total '.
        'FROM document d '.
        'join log_transaction lt '.
        'on d.document_iden=lt.document_iden '.
        'and d.void=\'N\' '.
        'and lt.transaction_type=\'INVOICE\' '.
        'join invoice i '.
        'on i.invoice_iden=lt.iden2 '.
        'and i.customer_iden = lt.iden1 '.
	'join document d1 on '.
	'd1.document_iden = i.invoice_iden '.
        'where d.document_date<=\''.$sql_asof_date.'\' '.
        'and d.owner_iden = \''.$owner_iden.'\' '.
        'and i.customer_iden = \''.$customer_iden.'\'';
      $query .= 'group by i.invoice_type,i.invoice_id ';
      $query .= 'having total<>0 ';
      $query .= 'order by i.invoice_type,d.document_date';
      $statement = $connection->prepare($query);
      $statement->execute();
      $this->detailed_receivables = $statement->fetchAll(PDO::FETCH_ASSOC);
      $this->setLayout('nomenu');
    
    }else{
      return $this->renderText('Errors encountered.');
    }
    
  }

  public function executeReceivables(sfWebRequest $request){
    $this->receivablesForm = new myReceivablesForm();
  }
  
  public function executeDoReceivables(sfWebRequest $request){
    $parameters = $request->getParameter('rkey_receivables');
    $receivablesForm = new myReceivablesForm();
    $receivablesForm->bind($parameters);
    if($receivablesForm->isValid()){
      $this->asof_date = $receivablesForm->getValue('asof_date');
      $owner_iden = $receivablesForm['owner_iden']->getValue();
      $accountOwner = AccountOwnerPeer::retrieveByPk($owner_iden);
      $this->owner = $accountOwner->getContact();
      $connection = Propel::getConnection();
      $sql_asof_date = date('Y-m-d',strtotime($this->asof_date));
      $query = 
        'SELECT c.iden,c.list_name, '.
	'sum(case when datediff(\''.$sql_asof_date.'\',date_add(d1.document_date,interval i.days_due day))<=0 then lt.debit-lt.credit else 0 end) total_notdue, '.
	'sum(case when datediff(\''.$sql_asof_date.'\',date_add(d1.document_date,interval i.days_due day))>0 and datediff(\''.$sql_asof_date.'\',date_add(d1.document_date,interval i.days_due day))<=30 then lt.debit-lt.credit else 0 end) total_due30, '.
	'sum(case when datediff(\''.$sql_asof_date.'\',date_add(d1.document_date,interval i.days_due day))>30 and datediff(\''.$sql_asof_date.'\',date_add(d1.document_date,interval i.days_due day))<=60 then lt.debit-lt.credit else 0 end) total_due60, '.
	'sum(case when datediff(\''.$sql_asof_date.'\',date_add(d1.document_date,interval i.days_due day))>60 and datediff(\''.$sql_asof_date.'\',date_add(d1.document_date,interval i.days_due day))<=90 then lt.debit-lt.credit else 0 end) total_due90, '.
	'sum(case when datediff(\''.$sql_asof_date.'\',date_add(d1.document_date,interval i.days_due day))>90 then lt.debit-lt.credit else 0 end) total_due120, '.
	'sum(lt.debit-lt.credit) AS total '.
        'FROM document d '.
        'join log_transaction lt '.
        'on d.document_iden=lt.document_iden '.
        'and d.void=\'N\' '.
        'and lt.transaction_type=\'INVOICE\' '.
        'join contact c '.
        'on c.iden=lt.iden1 '.
	'join invoice i '.
	'on i.invoice_iden = lt.iden2 '.
	'and lt.type2=\'INVOICE\' '.
        'join document d1 '.
	'on d1.document_iden = i.invoice_iden '.
        'where d.document_date<=\''.$sql_asof_date.'\' '.
        'and d.owner_iden = \''.$owner_iden.'\'';
      $query .= 'group by c.iden,c.list_name ';
      $query .= 'having total<>0 ';
      $query .='order by c.list_name';
      $statement = $connection->prepare($query);
      $statement->execute();
      $this->receivables = $statement->fetchAll(PDO::FETCH_ASSOC);
      $this->setLayout('nomenu');
    
    }else{
      return $this->renderText('Errors encountered.');
    }
    
  }
  
  public function executeInvoiceSummary(sfWebRequest $request){
    $customer_iden = $request->getParameter('contact_iden');
    $this->contact = ContactPeer::retrieveByPk($customer_iden);
    $this->invoiceSummaryForm = new myInvoiceSummaryForm();
    $this->invoiceSummaryForm->setDefault('customer_iden',$customer_iden);

  }
  
  public function executeDoInvoiceSummary(sfWebRequest $request){
      $myInvoiceSummaryForm = new myInvoiceSummaryForm();
      $myInvoiceSummaryForm->bind($request->getParameter('rkey_invoice_summary'));
      if ($myInvoiceSummaryForm->isValid()){
        $this->from_date = $myInvoiceSummaryForm->getValue('from_date');
        $this->to_date = $myInvoiceSummaryForm->getValue('to_date');
        $customer_iden = $myInvoiceSummaryForm['customer_iden']->getValue();
        $owner_iden = $myInvoiceSummaryForm['owner_iden']->getValue();
        $accountOwner = AccountOwnerPeer::retrieveByPk($owner_iden);
        $this->owner = $accountOwner->getContact();
        $accountCustomer = AccountCustomerPeer::retrieveByPk($customer_iden);
        $this->contact = $accountCustomer->getContact();
        $c = new Criteria();
        $c->addJoin(DocumentPeer::DOCUMENT_IDEN,InvoicePeer::INVOICE_IDEN);
        $from_date = $myInvoiceSummaryForm['from_date']->getValue();
        $from_date_str = $from_date['year'].'-'.$from_date['month'].'-'.$from_date['day'];
        $c->addAnd(DocumentPeer::DOCUMENT_DATE,$from_date_str,Criteria::GREATER_EQUAL);
        $to_date = $myInvoiceSummaryForm['to_date']->getValue();
        $to_date_str = $to_date['year'].'-'.$to_date['month'].'-'.$to_date['day'];
        $c->addAnd(DocumentPeer::DOCUMENT_DATE,$to_date_str,Criteria::LESS_EQUAL);
        $c->addJoin(ContactPeer::IDEN,InvoicePeer::CUSTOMER_IDEN);
        $c->add(InvoicePeer::CUSTOMER_IDEN,$myInvoiceSummaryForm['customer_iden']->getValue());
        $c->add(DocumentPeer::OWNER_IDEN,$myInvoiceSummaryForm['owner_iden']->getValue());
        $c->add(DocumentPeer::VOID,'N');
        $c->addAscendingOrderByColumn(InvoicePeer::INVOICE_TYPE);
        $c->addAscendingOrderByColumn(InvoicePeer::INVOICE_ID);
        $this->invoices = InvoicePeer::doSelect($c);
        $this->setLayout('nomenu');
      }else{
        return $this->renderText('Errors encountered.');
      }

  }
 
  public function executeChecksSummary(sfWebRequest $request){
    $this->checksSummaryForm = new myChecksSummaryForm();   
  } 
  
  public function executeDoChecksSummary(sfWebRequest $request){
    $parameters = $request->getParameter('rkey_checks_summary');
    $checksSummaryForm = new myChecksSummaryForm();
    $checksSummaryForm->bind($parameters);
    if($checksSummaryForm->isValid()){
      $this->from_date = $checksSummaryForm['from_date']->getValue();
      $this->to_date = $checksSummaryForm['to_date']->getValue();
      $owner_iden = $checksSummaryForm['owner_iden']->getValue();
      $accountOwner = AccountOwnerPeer::retrieveByPk($owner_iden);
      $this->owner = $accountOwner->getContact();
      $c = new Criteria();
      $c->add(DocumentPeer::VOID,'N');
      $c->addJoin(PaymentPeer::PAYMENT_IDEN,DocumentPeer::DOCUMENT_IDEN);
      $c->addJoin(PaymentPeer::PAYMENT_IDEN,PaymentChecksPeer::PAYMENT_IDEN);
      $this->from_date_str = $this->from_date['year'].'-'.$this->from_date['month'].'-'.$this->from_date['day'];
      $c->addAnd(PaymentChecksPeer::CHECK_DATE,$this->from_date_str,Criteria::GREATER_EQUAL);
      $this->to_date_str = $this->to_date['year'].'-'.$this->to_date['month'].'-'.$this->to_date['day'];
      $c->addAnd(PaymentChecksPeer::CHECK_DATE,$this->to_date_str,Criteria::LESS_EQUAL);
      $c->addAscendingOrderByColumn(PaymentChecksPeer::CHECK_DATE);
      $this->paymentCheckss = PaymentChecksPeer::doSelect($c);
      $this->setLayout('nomenu');
    }else{
      return $this->renderText('Errors encountered.');
    }
  }
  
}
